package com.example.monitor.repository;

import com.example.monitor.model.DO.AlarmRecord;
import com.example.monitor.model.VO.AlarmRecordVO;
import com.example.monitor.model.VO.UserAbnormalRecordVO;
import com.example.monitor.model.VO.WorkshopRankVO;
import com.example.monitor.model.enums.AlarmStatus;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.sql.Timestamp;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

@Repository
public interface AlarmRecordRepository extends JpaRepository<AlarmRecord, Long>, JpaSpecificationExecutor<AlarmRecord> {
    @Query("SELECT new com.example.monitor.model.VO.AlarmRecordVO(" +
            "ar.alarmId, " +
            "w.workerId, " +
            "w.name, " +
            "ar.workshop, " +
            "ar.alarmType, " +
            "ar.alarmTime, " +      // 直接使用Timestamp
            "ar.status, " +        // 直接使用枚举
            "ar.imageUrl) " +      // 映射到thumbnailUrl
            "FROM AlarmRecord ar LEFT JOIN ar.worker w " +
            "WHERE (:alarmType IS NULL OR ar.alarmType = :alarmType) " +
            "AND (:workshop IS NULL OR ar.workshop = :workshop) " +
            "AND (:status IS NULL OR ar.status = :status) " +
            "AND (:startTime IS NULL OR ar.alarmTime >= :startTime) " +
            "AND (:endTime IS NULL OR ar.alarmTime <= :endTime)")
    Page<AlarmRecordVO> findAlarmRecordsAsVO(
            @Param("alarmType") String alarmType,
            @Param("workshop") String workshop,
            @Param("status") AlarmStatus status,
            @Param("startTime") Timestamp startTime,
            @Param("endTime") Timestamp endTime,
            Pageable pageable
    );

    @Query("SELECT new com.example.monitor.model.VO.WorkshopRankVO(" +
            "a.workshop, COUNT(a.alarmId), " +
            "(SELECT a2.alarmType FROM AlarmRecord a2 " +
            "WHERE a2.workshop = a.workshop " +
            "GROUP BY a2.alarmType ORDER BY COUNT(a2.alarmId) DESC LIMIT 1)) " +
            "FROM AlarmRecord a " +
            "WHERE (:workshop IS NULL OR a.workshop = :workshop) " +
            "AND (:alarmType IS NULL OR a.alarmType = :alarmType) " +
            "AND a.alarmTime >= :startTime " +
            "GROUP BY a.workshop " +
            "ORDER BY COUNT(a.alarmId) DESC")
    List<WorkshopRankVO> findWorkshopAlarmRank(
            @Param("workshop") String workshop,
            @Param("alarmType") String alarmType,
            @Param("startTime") Timestamp startTime);

    // 获取日期范围内的所有日期
    @Query(value = "SELECT DISTINCT DATE(alarm_time) as date FROM alarm_records " +
            "WHERE alarm_time >= :startTime " +
            "ORDER BY date", nativeQuery = true)
    List<String> findDistinctDates(@Param("startTime") Timestamp startTime);

    // 获取各车间每日报警数量
    @Query("SELECT a.workshop, DATE(a.alarmTime) as date, COUNT(a.alarmId) as count " +
            "FROM AlarmRecord a " +
            "WHERE (:workshop IS NULL OR a.workshop = :workshop) " +
            "AND a.alarmTime >= :startTime " +
            "GROUP BY a.workshop, DATE(a.alarmTime) " +
            "ORDER BY DATE(a.alarmTime), a.workshop")
    List<Object[]> findDailyAlarmCount(
            @Param("workshop") String workshop,
            @Param("startTime") Timestamp startTime);

    @Query("SELECT a.alarmType, COUNT(a.alarmId) " +
            "FROM AlarmRecord a " +
            "WHERE (:workshop IS NULL OR a.workshop = :workshop) " +
            "AND a.alarmTime >= :startTime " +
            "GROUP BY a.alarmType " +
            "ORDER BY COUNT(a.alarmId) DESC")
    List<Object[]> countByAlarmType(
            @Param("workshop") String workshop,
            @Param("startTime") Timestamp startTime);

    @Query(value = "SELECT MONTH(alarm_time) as month, COUNT(*) as count " +
            "FROM alarm_records " +
            "WHERE worker_id = :workerId " +
            "AND YEAR(alarm_time) = :year " +
            "GROUP BY MONTH(alarm_time) " +
            "ORDER BY month", nativeQuery = true)
    List<Object[]> countMonthlyByWorker(
            @Param("workerId") String workerId,
            @Param("year") Integer year);

    @Query("SELECT a.alarmType, COUNT(a.alarmId) " +
            "FROM AlarmRecord a " +
            "WHERE a.worker.workerId = :workerId " +
            "GROUP BY a.alarmType " +
            "ORDER BY COUNT(a.alarmId) DESC")
    List<Object[]> countAbnormalTypesByWorker(@Param("workerId") String workerId);

    @Query("SELECT new com.example.monitor.model.VO.UserAbnormalRecordVO(" +
            "a.alarmTime, a.alarmType, a.status, a.imageUrl) " +
            "FROM AlarmRecord a " +
            "WHERE a.worker.workerId = :workerId " +
            "ORDER BY a.alarmTime DESC")
    Page<UserAbnormalRecordVO> findAbnormalRecordsByWorker(
            @Param("workerId") String workerId,
            Pageable pageable);

    // 根据alarmId查找记录（用于删除验证）
    Optional<AlarmRecord> findByAlarmId(Long alarmId);

}